import sqlite3
from pathlib import Path
from datetime import datetime
import xlrd

print('111')
print(datetime.now().strftime("%m"))

current_time = datetime.now().strftime('%Y-%m')
thispath = Path(__file__).resolve().parent.parent

class Handle():

    dbname = thispath.joinpath('db',current_time+'.db')
    def __init__(self):
        """
        连接dbname,没有就创建
        当Python在创建实例对象后自动执行init方法
        """
        self.__conn = sqlite3.connect(self.dbname)
        self.__cur = self.__conn.cursor()

    def close(self):
        """
        关闭连接
        :return:
        """
        self.__cur.close()
        self.__conn.close()

    def create(self,sqlfile):
        """
        建表  执行sql
        :return:
        """
        self.__cur.execute(sqlfile)
        self.__conn.commit()

    def delete(self,sql_name):
        """
        删除表 用drop
        删除表中字段用delete
        :param sql_name: 表名
        :return:
        """
        self.__cur.execute("""drop table if exists  {}""".format(sql_name))
        self.__conn.commit()
        print('{}表已删除'.format(sql_name))

    def load_xls(self,sql_name):
        """
        将xls的文件内容写入到sql
        :return:
        """
        dir = thispath.joinpath('excel','巡视类型字典表.xls')
        book = xlrd.open_workbook(dir)
        sheet = book.sheet_by_index(0) # Select the first sheet

        title = sheet.row_values(0) # 获取第一行表头
        # print('表头：',title)

        sql = """insert into 'dict' values (?,?,?)"""

        for row_index in range(1,sheet.nrows): # sheet.nrows  行数。从第二行（除去表头）开始
            row = sheet.row_values(row_index)
            # print('行数：',row_index)
            # print('数据：',row)
            id = row_index
            text = row[0]
            value = row[1]
            values = (id,text,value)
            # print(values)

            self.__cur.execute(sql,values)
            self.__conn.commit()
        print('数据已写入{}.sql'.format(sql_name))



if __name__ == '__main__':
    handle = Handle()

    sql_name = 'dict'
    # 先删除表 以免重复写入数据
    handle.delete(sql_name)

    # 读取指定文件 建表
    # dict
    sql_file = thispath.joinpath('sql',sql_name+'.sql')
    with open(sql_file,mode='r') as f:
        content=f.read()
    print(content)
    handle.create(content)


    # 将excel数据导入到sql中
    handle.load_xls(sql_name)

    #表 mix_sqladd  验证 insert语法
    sql_name1 = 'mixed_sqladd'
    sql_file = thispath.joinpath('sql',sql_name1+'.sql')
    with open(sql_file,mode='r') as f:
        content=f.read()
    print(content)
    handle.delete(sql_name1)
    handle.create(content)
    insert_sql = """ 
                    insert into mixed_sqladd
                    select * from dict ;
                    """
    handle.create(insert_sql)


    # 关闭连接
    handle.close()